package com.gcloud.persistance;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.example.gcode.R;
import com.gcloud.dto.GCloudAccount;
import com.gcloud.dto.GCloudFile;
import com.utils.Constants;
import com.utils.GCloudUtil;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.util.Log;

public class SQLiteHandler extends SQLiteOpenHelper {

	public SQLiteHandler(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	/**
	 * Create the index table and account table
	 */

	@Override
	public void onCreate(SQLiteDatabase arg0) {
		arg0.execSQL(Querys.createAccountTable);
		arg0.execSQL(Querys.createTableIndex);
	}

	/**
	 * Method not implemented, GCloud won't to drop its tables
	 */

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub

	}

	/**
	 * Temporal method to test
	 * 
	 * @param word
	 */
	public void insert(String word) {
		Log.i("inserting: ", word);
		ContentValues values = new ContentValues();
		values.put("GAME", word);
		this.getWritableDatabase().insert("play", null, values);
		closeConnection();
	}

	/**
	 * Open new connection
	 */

	public void openConnection() {
		this.getWritableDatabase();
	}

	/**
	 * Close actual connection
	 */

	public void closeConnection() {
		this.close();
	}

	/**
	 * Method use to insert a new file in index table
	 * 
	 * @param key
	 * @param name
	 * @param description
	 * @param mimeType
	 * @param account
	 * @param uri
	 * @param isDirectory
	 * @param size
	 * @param service
	 * @param isFavorite
	 */

	public void insertFile(String key, String name, String description,
			String mimeType, String account, String uri, boolean isDirectory,
			long size, int service, boolean isFavorite) {
		insertAccount(String.valueOf(service), account, key, key);
		ContentValues fileProperties = new ContentValues();
		fileProperties.put(Constants.FILE_KEY, key);
		fileProperties.put(Constants.FILE_NAME, name);
		fileProperties.put(Constants.FILE_DESCRIPTION, description);
		fileProperties.put(Constants.FILE_MIMETYPE, mimeType);
		fileProperties.put(Constants.FILE_ACCOUNT, account);
		fileProperties.put(Constants.FILE_URL, uri);
		fileProperties.put(Constants.FILE_SIZE, size);
		fileProperties.put(Constants.FILE_IS_DIRECTORY, isDirectory);
		fileProperties.put(Constants.FILE_SERVICE, service);
		fileProperties.put(Constants.FILE_FAVORITE, isFavorite);
		insertFile(fileProperties);
	}

	/**
	 * Insert a <code>List<ContentValues></code>
	 * 
	 * @param files
	 */

	public void insertFiles(List<ContentValues> files) {
		// Log.i("SQLiteHandler", "going to persist: " + files.size());
		for (ContentValues contentValues : files) {
			insertFile(contentValues);
		}
	}

	/**
	 * Persist a <code>ContentValues</code> Object
	 * 
	 * @param fileProperties
	 */

	private void insertFile(ContentValues fileProperties) {
		// Log.i("inserting file: ", "" + fileProperties.hashCode());
		try {
			this.getWritableDatabase().insert(Constants.INDEX_FILE_SERVICE,
					null, fileProperties);
		} catch (Exception sqe) {
			// Don't care file already exists
		} finally {
			closeConnection();
		}
	}

	public void insertAccount(String service, String userAccount,
			String passAccount) {
		insertAccount(service, userAccount, null, null);
	}

	public void insertAccount(String service, String account, String key,
			String keySecret) {
		try {
			Log.d("inserting account", account);
			ContentValues values = new ContentValues();
			values.put(Constants.FILE_SERVICE, service);
			values.put(Constants.FILE_ACCOUNT, account);
			values.put(Constants.FILE_KEY, key);
			values.put(Constants.KEY_SECRET, keySecret);
			this.getWritableDatabase().insert(Constants.ACCOUNT, null, values);
			closeConnection();
		} catch (SQLiteConstraintException SQ) {

		}
	}

	/**
	 * Check as favorite a file
	 * 
	 * @param key
	 */

	public void checkAsFavorite(String key) {
		checkAsFavorite(key, 1);
	}

	/**
	 * Uncheck as favorite a file
	 * 
	 * @param key
	 */
	public void uncheckAsFavorite(String key) {
		checkAsFavorite(key, 0);
	}

	/**
	 * Check a file as favorite.
	 * 
	 * @param key
	 * @param value
	 */

	public void checkAsFavorite(String key, int value) {
		ContentValues fileNewValue = new ContentValues();
		fileNewValue.put(Constants.FILE_FAVORITE, value);
		try {
			this.getWritableDatabase().update(Constants.INDEX_FILE_SERVICE,
					fileNewValue, Constants.FILE_KEY + " = ?",
					new String[] { key });
			closeConnection();
			Log.i("SQLiteHandler-checkAsFavorite()", "Key: " + key + " Rate: "
					+ value);
		} catch (Exception e) {
			Log.e("SQLiteHandler-checkAsFavorite()", "Key: " + key + " Rate: "
					+ value);
		}
	}

	public void checkAsChild(String key) {
		ContentValues fileNewValue = new ContentValues();
		fileNewValue.put(Constants.FILE_IS_ROOT, 1);
		try {
			this.getWritableDatabase().update(Constants.INDEX_FILE_SERVICE,
					fileNewValue, Constants.FILE_KEY + " = ?",
					new String[] { key });
			closeConnection();
			Log.i("SQLiteHandler-checkAsChild()", "Key: " + key);
		} catch (Exception e) {
			Log.e("SQLiteHandler-checkAsChild()", "Key: " + key);
		}
	}

	/**
	 * Delete a file from local database specifying the key
	 * 
	 * @param key
	 */

	public void deleteFile(String key) {
		this.getWritableDatabase().delete(Constants.INDEX_FILE_SERVICE,
				Constants.FILE_KEY + " = ?", new String[] { key });
		closeConnection();
	}

	public ArrayList<GCloudFile> getChildren(String parentKey) {
		ArrayList<GCloudFile> childrens;
		try {
			childrens = getFileList("PARENT=?", new String[] { parentKey });
		} catch (SQLException e) {
			Log.i(Constants.GCLOUD_DB_ERROR, ""
					+ Constants.RETRIEVING_FILE_ERROR);
			childrens = new ArrayList<GCloudFile>();
		}
		return childrens;
	}

	/**
	 * Return a select order by name
	 * 
	 * @return <b>List<GCloudFile></b>
	 * @throws SQLException
	 */

	public ArrayList<GCloudFile> getFileList(String whereClause,
			String[] selectionArgs) throws SQLException {
		return getFileList(whereClause, selectionArgs, Constants.FILE_ACCOUNT+","+Constants.FILE_SERVICE+","+Constants.FILE_NAME);
	}

	/**
	 * Return select clause order by <param><code>String</code> OrderBy</param>
	 * 
	 * @param whereClause
	 * @param selectionArgs
	 * @param OrderBy
	 * @return <code>List<GCloudFile></code>
	 * @throws SQLException
	 */

	public ArrayList<GCloudFile> getFileList(String whereClause,
			String[] selectionArgs, String OrderBy) throws SQLException {
		ArrayList<GCloudFile> fileList = new ArrayList<GCloudFile>();
		Cursor cursor = this.getWritableDatabase().query(true,
				Constants.INDEX_FILE_SERVICE, null, whereClause, selectionArgs,
				null, null, OrderBy, null);
		// this.getWritableDatabase().query(
		// true,
		// Constants.INDEX_FILE_SERVICE,
		// new String[] { Constants.FILE_KEY, Constants.FILE_ACCOUNT,
		// Constants.FILE_DESCRIPTION, Constants.FILE_FAVORITE,
		// Constants.FILE_IS_DIRECTORY, Constants.FILE_MIMETYPE,
		// Constants.FILE_SERVICE, Constants.FILE_SIZE,
		// Constants.FILE_URL, Constants.FILE_NAME }, whereClause,
		// selectionArgs, null, null, Constants.FILE_NAME, null);

		if (cursor.moveToFirst()) {
			do {
				GCloudFile gCloudFile = new GCloudFile();
				gCloudFile = getFile(cursor);
				fileList.add(gCloudFile);
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
			this.close();
		}
		return fileList;
	}

	public void getSavedList() throws SQLException {
		Log.i("row", "minimum");

		Cursor cursor = this.getReadableDatabase().query("play",
				new String[] { "id", "GAME" }, null, null, null, null, null);

		if (cursor.moveToFirst()) {
			do {
				Log.i("sqlitelist: ",
						cursor.getString(cursor.getColumnIndex("GAME")) + "  "
								+ cursor.getInt(cursor.getColumnIndex("id")));

			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
	}

	/**
	 * 
	 * @param key
	 * @return <code>GCloudFile</code> from DB
	 */

	public GCloudFile getFile(String key) {
		Cursor cursor = this.getWritableDatabase().query(
				Constants.INDEX_FILE_SERVICE, null, "key=?",
				new String[] { key }, null, null, null);
		GCloudFile gCloudFile = new GCloudFile();
		// Cursor cursor = this.getWritableDatabase().rawQuery(
		// "select * from " + Constants.INDEX_FILE_SERVICE
		// + " where PARENT=?", new String[] { key });
		if (cursor != null) {
			Log.i("is not null: ", "is not null");
		}
		if (cursor.moveToFirst()) {
			Log.i("move to first", "move to first");
			gCloudFile = getFile(cursor);
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
			this.close();
		}
		return gCloudFile;
	}

	/**
	 * Convert the cursor's file to <b>GCloudFile</b>
	 * 
	 * @param cursor
	 * @return
	 */

	public GCloudFile getFile(Cursor cursor) {
		GCloudFile gCloudFile = new GCloudFile();
		gCloudFile.setAccount(cursor.getString(cursor
				.getColumnIndexOrThrow(Constants.FILE_ACCOUNT)));
		gCloudFile.setService(cursor.getString(cursor
				.getColumnIndexOrThrow(Constants.FILE_SERVICE)));
		gCloudFile.setDescription(cursor.getString(cursor
				.getColumnIndexOrThrow(Constants.FILE_DESCRIPTION)));
		gCloudFile.setKey(cursor.getString(cursor
				.getColumnIndexOrThrow(Constants.FILE_KEY)));
		gCloudFile.setMimeType(cursor.getString(cursor
				.getColumnIndexOrThrow(Constants.FILE_MIMETYPE)));
		gCloudFile.setName(cursor.getString(cursor
				.getColumnIndexOrThrow(Constants.FILE_NAME)));
		gCloudFile.setSize(cursor.getLong(cursor
				.getColumnIndexOrThrow(Constants.FILE_SIZE)));
		gCloudFile.setFavoriteRate(cursor.getInt(cursor
				.getColumnIndexOrThrow(Constants.FILE_FAVORITE)));
		int directory = cursor.getInt((cursor
				.getColumnIndexOrThrow(Constants.FILE_IS_DIRECTORY)));
		gCloudFile.setDirectory(directory == 1 ? true : false);
		// int isRoot = cursor.getInt((cursor
		// .getColumnIndexOrThrow(Constants.FILE_IS_ROOT)));
		// gCloudFile.setRoot(isRoot == 1 ? true : false);
//		String path = cursor.getString(cursor
//				.getColumnIndexOrThrow(Constants.FILE_URL));
//		if (path != null)
			gCloudFile.setUrl(cursor.getString(cursor
					.getColumnIndexOrThrow(Constants.FILE_URL)));
		if (gCloudFile.getService().equalsIgnoreCase(Constants.DROPBOX)) {
			gCloudFile.setServiceIcon(R.drawable.dropbox);
		} else {
			gCloudFile.setServiceIcon(R.drawable.googledrive);
		}
		if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.adoc)) {
			gCloudFile.setFileTypeIcon(R.drawable.doc);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.adocx)) {
			gCloudFile.setFileTypeIcon(R.drawable.docx);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.axls)) {
			gCloudFile.setFileTypeIcon(R.drawable.xls);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.axlsx)) {
			gCloudFile.setFileTypeIcon(R.drawable.xlsx);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.apdf)) {
			gCloudFile.setFileTypeIcon(R.drawable.pdf);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.arar)) {
			gCloudFile.setFileTypeIcon(R.drawable.rar);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.azip)) {
			gCloudFile.setFileTypeIcon(R.drawable.zip);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.atxt)) {
			gCloudFile.setFileTypeIcon(R.drawable.txt);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.ajpg)) {
			gCloudFile.setFileTypeIcon(R.drawable.jpg);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.apng)) {
			gCloudFile.setFileTypeIcon(R.drawable.png);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.docu)) {
			gCloudFile.setFileTypeIcon(R.drawable.gdoc);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.pres)) {
			gCloudFile.setFileTypeIcon(R.drawable.gppt);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.agif)) {
			gCloudFile.setFileTypeIcon(R.drawable.png);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.ajava)) {
			gCloudFile.setFileTypeIcon(R.drawable.java);
		} else if (GCloudUtil
				.equals(gCloudFile.getMimeType(), Constants.adjava)) {
			gCloudFile.setFileTypeIcon(R.drawable.java);
		} else if (GCloudUtil.equals(gCloudFile.getMimeType(), Constants.form)) {
			gCloudFile.setFileTypeIcon(R.drawable.gsheet);
		} else if (GCloudUtil
				.equals(gCloudFile.getMimeType(), Constants.ssheet)) {
			gCloudFile.setFileTypeIcon(R.drawable.gsheet);
		} else if (directory == 1) {
			gCloudFile.setFileTypeIcon(R.drawable.folder);
		} else {
			gCloudFile.setFileTypeIcon(R.drawable.blank);
		}
		return gCloudFile;
	}

	public ArrayList<GCloudAccount> getAccountList() throws SQLException {

		ArrayList<GCloudAccount> accountList = new ArrayList<GCloudAccount>();
		Cursor cursor = this.getWritableDatabase().query(true,
				Constants.ACCOUNT, null, null, null, null, null, null, null);
		if (cursor.moveToFirst()) {
			do {
				GCloudAccount gCloudAccount = new GCloudAccount();
				gCloudAccount.setAccount(cursor.getString(cursor
						.getColumnIndexOrThrow(Constants.FILE_ACCOUNT)));
				gCloudAccount.setService(cursor.getString(cursor
						.getColumnIndexOrThrow(Constants.FILE_SERVICE)));
				gCloudAccount.setKey(cursor.getString(cursor
						.getColumnIndexOrThrow(Constants.FILE_KEY)));
				gCloudAccount.setKeySecret(cursor.getString(cursor
						.getColumnIndexOrThrow(Constants.KEY_SECRET)));
				gCloudAccount.setId(cursor.getInt(cursor
						.getColumnIndexOrThrow(Constants.ID)));
				accountList.add(gCloudAccount);
				gCloudAccount.setActive(cursor.getInt(cursor
						.getColumnIndexOrThrow(Constants.ACTIVE)));
				accountList.add(gCloudAccount);
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
			this.close();
		}
		return accountList;
	}

	public String[] getKeyAccount(String key) {
		Cursor cursor = this.getWritableDatabase().query(Constants.ACCOUNT,
				null, "ACCOUNT=?", new String[] { key }, null, null, null);
		String keys[] = new String[2];
		if (cursor != null) {
			Log.i("is not null: ", "is not null");
		}
		if (cursor.moveToFirst()) {
			Log.i("move to first", "move to first");
			keys[0] = cursor.getString(cursor
					.getColumnIndexOrThrow(Constants.FILE_KEY));
			keys[1] = cursor.getString(cursor
					.getColumnIndexOrThrow(Constants.KEY_SECRET));
			Log.i("move to first key", keys[0]);
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
			this.close();
		}
		return keys;
	}

}
